/*
 * ClassTable.java - SQL operations with the table 'class'
 *  in the database 'context' (copy of WebDeso).
 *
 * Copyright (c) 2007 Computer-Aided Integrated Systems Laboratory, 
 * St. Petersburg Institute for Informatics and Automation RAS http://cais.lisa.iias.spb.su
 */

package dbcontext;

import dbindex.Language;

import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.SQLException;

/** Routines to work with the table 'class' in the database 'context'.
 */
public class ClassTable extends RelationTable {
    
    /** class's id in ontology */
    public int      class_id;
    
    /** id of ontology that is compared against the document*/
    public int      onto_id;
    
    /** id of base ontology*/
    public int      base_onto_id;
    
    /** id of parent class in ontology */
    public int      parent_id;
    
    /** id, title (name), and description of class in the table 'term' */
    public TermTable term;
    
    /** id of class name in the table term */
    //public int      term_id;
    
    /** obsolete, it was used in MAccess before MySQL */
    public int      session_id;
    
    /** id of ??? */
    public int      merging_onto_id;
    
    
    /** Creates a new instance of ClassTable */
    public ClassTable() {
    }
    
    /** Inserts element to the table 'class' in MySQL database.
     * 
     * SQL example:
     * INSERT INTO term (lang_id, text, description) VALUES (0, "title12", "desc12");
     * INSERT INTO class (onto_id,base_onto_id,id,parent_id,term_id) VALUES(101,102,41,42,12);<br>
     *
     * UPDATE class SET onto_id=101,base_onto_id=102,parent_id=42,term_id=12 WHERE id=41;<br>
     *
     * Test database content by
     * SELECT * FROM class,term WHERE class.term_id=term.id LIMIT 0,1000<br>
     *
     *  @param parent_id    id of parent class
     *  @param class_desc   description of class (stored in the table term)
     */
    public static void insert ( java.sql.Connection conn, Language lang,
                                int class_id, int parent_id,
                                int onto_id,  int base_onto_id,
                                String class_name,String class_desc) {
        
        Statement   s = null;
        ResultSet   rs= null;
        String      str_sql = null;        
        try 
        {
            s = conn.createStatement ();
            
            int term_id = TermTable.insertAndGetID(conn, lang, class_name, class_desc);
            ClassTable c = get(conn, lang, onto_id, class_name);
            if (c == null)
            {
                // IF there is no triple (lang,onto_id,class_name) in tables 'term' and 'class'
                //   if there is no record with class_id in the table 'class'
                //     then insert 
                //   else update WHERE class_id 
                // ENDIF
                
                c = getByClassID(conn, class_id);
                if (c != null) 
                {   // UPDATE
	            str_sql = "UPDATE class SET" + 
                                    " onto_id=" + onto_id + 
                                    ",base_onto_id="+ base_onto_id + 
                                    ",parent_id=" + parent_id +
                                    ",term_id="+ term_id + 
                              " WHERE id="+ class_id;
                } else {
                    // INSERT
	            str_sql = "INSERT INTO class (onto_id,base_onto_id,id,parent_id,term_id) "+ 
                                    "VALUES (" + onto_id + 
                                    ","+ base_onto_id + 
                                    ","+ class_id + 
                                    "," + parent_id +
                                    ","+ term_id + ")";
                }
                s.executeUpdate (str_sql);
            } 
        }catch(SQLException ex) {
            System.err.println("SQLException (context.ClassTable.java insert()):: sql='" + str_sql + "' " + ex.getMessage());
        } finally {
            if (rs != null) {   try { rs.close(); } catch (SQLException sqlEx) { }  rs = null; }
            if (s != null)  {   try { s.close();  } catch (SQLException sqlEx) { }  s = null;  }
        }
    }
    
    
    /** Selects (parent_id,onto_id,base_onto_id,term_id) from the table 'class'
     * by the class id (UNIQUE PRIMARY KEY in the table).
     *
     *  SQL:
     *  SELECT parent_id,onto_id,base_onto_id,term_id FROM class WHERE id=3;
     *  
     *  @param onto_id     id of ontology
     *  @param term_id     id of class title in the table 'term'
     *  @return ClassTable object with initialized fields: parent_id, onto_id, base_onto_id, term_id
     */
    public static ClassTable getByClassID (java.sql.Connection conn,int class_id) {
        ClassTable cc = null;
        Statement   s = null;
        ResultSet   rs= null;
        String      str_sql = null;
        try {
            s = conn.createStatement ();
            str_sql = "SELECT parent_id,onto_id,base_onto_id,term_id FROM class WHERE id=" + class_id;
            s.executeQuery (str_sql);
            
            rs = s.getResultSet ();
            if (rs.next ())
            {
                cc = new ClassTable();
                cc.class_id = class_id;
                cc.id       = class_id;
                cc.parent_id    = rs.getInt("parent_id");
                cc.onto_id      = rs.getInt("onto_id");
                cc.base_onto_id = rs.getInt("base_onto_id");
                cc.term         = TermTable.getByID(conn, rs.getInt("term_id"));
            }
        } catch(SQLException ex) {
            System.err.println("SQLException (Context.ClassTable.java get()):: sql='" + str_sql + "' " + ex.getMessage());
        } finally {
            if (rs != null) {   try { rs.close(); } catch (SQLException sqlEx) { }  rs = null; }
            if (s != null)  {   try { s.close();  } catch (SQLException sqlEx) { }  s = null;  }
        }
        
        return cc;
    }
    
    
    /** Selects (class_id,parent_id,base_onto_id) from the table 'class'
     * by the ontology id and class name.
     *
     *  SQL:
     *  SELECT id FROM term WHERE lang_id=0 AND text="class_name'_\"insert"
     *  13
     *  SELECT id,parent_id,base_onto_id FROM class WHERE onto_id=3 AND term_id=13;
     *  
     *  @param onto_id      id of ontology
     *  @param class_name   name of class title in the table 'term'
     *  @return ClassTable object with initialized fields: class_id, parent_id, base_onto_id, term
     */
    public static ClassTable get (java.sql.Connection conn,Language lang,int onto_id,String class_name) {
        
        ClassTable cc = null;
        Statement   s = null;
        ResultSet   rs= null;
        String      str_sql = null;
        
        int term_id = TermTable.getID(conn, lang, class_name);
        if(0 == term_id) {
            return null;
        }
        
        try {
            s = conn.createStatement ();
            str_sql = "SELECT id,parent_id,base_onto_id FROM class WHERE onto_id=" + onto_id + " AND term_id=" + term_id;
            s.executeQuery (str_sql);
            
            rs = s.getResultSet ();
            if (rs.next ())
            {
                cc = new ClassTable();
                cc.class_id = rs.getInt("id");
                cc.id       = rs.getInt("id");
                cc.parent_id = rs.getInt("parent_id");
                cc.onto_id = onto_id;
                cc.base_onto_id = rs.getInt("base_onto_id");
                cc.term = TermTable.getByID(conn, term_id);
            }
        } catch(SQLException ex) {
            System.err.println("SQLException (Context.ClassTable.java get()):: sql='" + str_sql + "' " + ex.getMessage());
        } finally {
            if (rs != null) {   try { rs.close(); } catch (SQLException sqlEx) { }  rs = null; }
            if (s != null)  {   try { s.close();  } catch (SQLException sqlEx) { }  s = null;  }
        }
        
        return cc;
    }
    
    /** Deletes class'es row by class name (stored in the 'term' table) and
     *  ontolody ID.
     *  SQL:        
     *  DELETE FROM class WHERE onto_id=101 AND term_id=12;
     */
    public static void delete (java.sql.Connection conn,Language lang,int onto_id,String class_name) {
        
        TermTable   term = null;
        Statement   s = null;
        ResultSet   rs= null;
        String      str_sql = null;
        
        int term_id = TermTable.getID(conn, lang, class_name);
        if(0 == term_id) {
            return;
        }
        
        ClassTable c = get(conn, lang, onto_id, class_name);
        if(null != c)
            ClassAttrTable.deleteByClassID(conn, c.class_id);
        
        try {
            s = conn.createStatement ();
            str_sql = "DELETE FROM class WHERE onto_id="+onto_id+" AND term_id="+term_id;
            s.execute(str_sql);
            
        } catch(SQLException ex) {
            System.err.println("SQLException (Context.ClassTable.java delete()):: sql='" + str_sql + "' " + ex.getMessage());
        } finally {
            if (rs != null) {   try { rs.close(); } catch (SQLException sqlEx) { }  rs = null; }
            if (s != null)  {   try { s.close();  } catch (SQLException sqlEx) { }  s = null;  }
        }
    }
}
